package com.qf.fmw.jdbc;

import com.qf.fmw.enity.Admin;
import com.qf.fmw.utils.DBUtils;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;

public class jdbcTest {

    //添加
    @Test
    public void testInsert() {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DBUtils.getConnection();
            //通过 Connection 对象创建 Statement 对象
            statement = connection.createStatement();
            //通过 Statement 对象执行 SQL 语句并获取结果集
            int resultSet = statement.executeUpdate("INSERT INTO admin(username, password, sex, love) VALUES ('z8', 138666666, '女', '听歌')");

            System.out.println("影响行数:" + resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(statement, connection);
        }
    }

    //查询所有
    @Test
    public void testSelect() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
       Admin admin = null;
        ArrayList<Admin> list = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM admin WHERE is_detele=1");
            //删除
            while (resultSet.next()){
                //1.根据字段名获取字段数据
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String sex = resultSet.getString("sex");
                String love = resultSet.getString("love");
                int isDetele = resultSet.getInt("is_detele");

                //2.根据列数获取字段数据
                /*int id = resultSet.getInt(1);
                String username = resultSet.getString(2);
                String password = resultSet.getString(3);
                String sex = resultSet.getString(4);
                String love = resultSet.getString(5);
                int isDetele = resultSet.getInt(6);*/
                //存放到对象中
               admin = new Admin(id, username,password,sex,love,isDetele);
               list.add(admin);
            }
            //遍历集合
            list.forEach(System.out::println);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.closeAll(resultSet, statement, connection);
        }
    }

    //修改
    @Test
    public void testUpdate() {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DBUtils.getConnection();
            statement = connection.createStatement();
            //int resultSet = statement.executeUpdate("DELETE FROM admin WHERE id=22");
            int resultSet = statement.executeUpdate("UPDATE admin SET username='z8',password=123456,sex='女' WHERE id=19");
            System.out.println("影响行数:" + resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(statement, connection);
        }
    }

    //删除
    @Test
    public void testDelete() {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DBUtils.getConnection();
            statement = connection.createStatement();
            int resultSet = statement.executeUpdate("DELETE FROM admin WHERE id=22");
            System.out.println("影响行数:" + resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(statement, connection);
        }
    }
}
